
-- **************************************************************************
--    Project Name:   经营指标-指标库应用网点维度日表
--    Job Name:       jms_dwm.dm_overall_target_network_sum_day_dt
--    Author :        季修魁
--    date：          2023/06/27
--    modify :        季修魁
--    date：          2023/07/25
-- **************************************************************************
-- **************************************************************************

---1.业务量达成率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select a.dt                    as stat_date
       ,'D'                    as stat_period
       ,a.network_code         as network_code
       ,a.network_name         as network_name
       ,'业务量达成率-网点经营'    as kpi_name
       ,2                      as kpi_type
       ,SUM(a.scan_cnt)        as kpi_fz
       ,b.collect_target       as kpi_fm
       ,null                   as kpi_values
       ,100                    as kpi_param
       ,'季修魁'                as kpi_user
       ,'3天'                  as redo_date_cnt
       ,'jms_dm.dm_cust_target_analysis_sum_day_dt/jms_dim.dim_tab_business_collettarget_cfg'  as remark
       ,'t-1'                  as kpi_timely
       ,a.dt                   as dt
       ,'nwm0000000091'        AS kpi_code
 from (select network_code
              ,network_name
              ,sum(taking_cnt)  scan_cnt
              ,dt
           from jms_dm.dm_cust_target_analysis_sum_day_dt
          where dt>=date_sub('{{ execution_date | cst_ds }}',3) and dt <='{{ execution_date | cst_ds }}'
           group by network_code,network_name,dt
       ) a
    left join jms_dim.dim_tab_business_collettarget_cfg b
      on a.network_code = b.city_code
   group by a.network_code,a.network_name,b.collect_target,a.dt
distribute by dt,kpi_code
;

---2.订单取消率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt              as stat_date
       ,'D'               as stat_period
       ,pick_network_code      as network_code
       ,pick_network_name      as network_name
       ,'订单取消率'    as kpi_name
       ,2                 as kpi_type
       ,sum(case when order_status_code = '104' then 1 else 0 end )   as kpi_fz
       ,count(1)     as kpi_fm
       ,sum(case when order_status_code = '104' then 1 else 0 end )   as kpi_values
       ,100               as kpi_param
       ,'季修魁'          as kpi_user
       ,'3天'             as redo_date_cnt
       ,'网点维度指标，取订单表jms_dwd.dwd_yl_oms_oms_order_incre_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'ccm00000049'     AS kpi_code
  from jms_dwd.dwd_yl_oms_oms_order_incre_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',3) and dt <='{{ execution_date | cst_ds }}'
 group by pick_network_code,pick_network_name,dt
 distribute by dt,kpi_code
;

---3.揽收率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
  from (
select dt                      as stat_date
       ,'D'                    as stat_period
       ,sender_network_code    as network_code
       ,sender_network_name    as network_name
       ,'揽收及时率'             as kpi_name
       ,2                      as kpi_type
       ,sum(timely_cnt)        as kpi_fz
       ,sum(all_cnt)           as kpi_fm
       ,sum(timely_cnt)        as kpi_values
       ,100                    as kpi_param
       ,'苏宁'                 as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_taking_timely_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000082'     AS kpi_code
  from jms_dm.dm_taking_timely_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <='{{ execution_date | cst_ds }}'
 group by sender_network_code,sender_network_name,dt
 union all
select dt                      as stat_date
       ,'D'                    as stat_period
       ,sender_network_code    as network_code
       ,sender_network_name    as network_name
       ,'七星潭揽收及时率'        as kpi_name
       ,2                      as kpi_type
       ,sum(timely_cnt)        as kpi_fz
       ,sum(all_cnt)           as kpi_fm
       ,sum(timely_cnt)        as kpi_values
       ,100                    as kpi_param
       ,'苏宁'                  as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_taking_timely_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis0000000564'   as kpi_code
  from jms_dm.dm_taking_timely_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <='{{ execution_date | cst_ds }}'
 and order_source_name = '七星潭'
 group by sender_network_code,sender_network_name,dt
) a
distribute by dt,kpi_code
;

---4.交件准点率2

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                      as stat_date
       ,'D'                    as stat_period
       ,receive_network_code   as network_code
       ,receive_network_name   as network_name
       ,'交件准点率2'           as kpi_name
       ,2                      as kpi_type
       ,sum(hand_over_in_time_num)          as kpi_fz
       ,sum(should_hand_over_num)           as kpi_fm
       ,sum(hand_over_in_time_num)          as kpi_values
       ,100                    as kpi_param
       ,'熊伟'                 as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_waybill_collect_intime_taking_agg'  as remark
       ,'t-2'             as kpi_timely
       ,dt  as dt
       ,'bis00000304'     AS kpi_code
  from jms_dm.dm_waybill_collect_intime_taking_agg
 where dt >=date_sub('{{ execution_date | cst_ds }}',2) and dt <=date_sub('{{ execution_date | cst_ds }}',1)
 group by receive_network_code,receive_network_name,dt
distribute by dt,kpi_code
;

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                   as stat_date
       ,'D'                    as stat_period
       ,receive_network_code   as network_code
       ,receive_network_name   as network_name
       ,'交件准点率1'             as kpi_name
       ,2                      as kpi_type
       ,sum(hand_over_in_time_num)          as kpi_fz
       ,sum(should_hand_over_num)           as kpi_fm
       ,sum(hand_over_in_time_num)          as kpi_values
       ,100                    as kpi_param
       ,'熊伟'                  as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_waybill_collect_intime_taking_agg'  as remark
       ,'t-1'             as kpi_timely
       , dt  as dt
       ,'bis00000136'     AS kpi_code
  from jms_dm.dm_waybill_collect_intime_taking_agg
where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <= '{{ execution_date | cst_ds }}'
group by receive_network_code,receive_network_name,dt
distribute by dt,kpi_code;


---5.准点签收率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                      as stat_date
       ,'D'                    as stat_period
       ,final_sign_network_code    as network_code
       ,final_sign_network_name    as network_name
       ,'时效签收准点率'           as kpi_name
       ,2                          as kpi_type
       ,sum(all_sign_22)           as kpi_fz
       ,sum(need_sign_count)       as kpi_fm
       ,sum(all_sign_22)           as kpi_values
       ,100                        as kpi_param
       ,'侯文龙'                   as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_terminal_sign_punctuality_rate_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000004'              as kpi_code
  from jms_dm.dm_terminal_sign_punctuality_rate_dt
 where dt='{{ execution_date | cst_ds }}'
 group by final_sign_network_code,final_sign_network_name,dt
distribute by dt,kpi_code
;

---6.  ---3日滞留率1/2

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                      as stat_date
       ,'D'                    as stat_period
       ,final_sign_network_code    as network_code
       ,final_sign_network_name    as network_name
       ,'3日滞留率1    '           as kpi_name
       ,2                          as kpi_type
       ,sum(retention_count_3d)           as kpi_fz
       ,sum(need_sign_count_3d)           as kpi_fm
       ,sum(retention_count_3d)           as kpi_values
       ,100                        as kpi_param
       ,'侯文龙'                   as kpi_user
       ,'5天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_network_retention_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000378'              as kpi_code
  from jms_dm.dm_network_retention_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',5) and dt <='{{ execution_date | cst_ds }}'
   and is_contain_difficult = '2'
 group by final_sign_network_code,final_sign_network_name,dt
union all
select dt                      as stat_date
       ,'D'                    as stat_period
       ,final_sign_network_code    as network_code
       ,final_sign_network_name    as network_name
       ,'3日滞留率2    '           as kpi_name
       ,2                          as kpi_type
       ,sum(retention_count_3d)           as kpi_fz
       ,sum(need_sign_count_3d)           as kpi_fm
       ,sum(retention_count_3d)           as kpi_values
       ,100                        as kpi_param
       ,'侯文龙'                   as kpi_user
       ,'5天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_network_retention_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000039'              as kpi_code
  from jms_dm.dm_network_retention_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',5) and dt <='{{ execution_date | cst_ds }}'
   and is_contain_difficult = '1'
 group by final_sign_network_code,final_sign_network_name,dt
 distribute by dt,kpi_code
 ;


---7-1.客诉率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'客诉率'                   as kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,null                       as kpi_values
       ,10000                      as kpi_param
       ,'申家明'                   as kpi_user
       ,'当月'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.jms_dm.dm_waybill_customer_complaint_detail_new_dt+jms_dm.dm_customer_complaint_network_arr_pick_summary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000009'              as kpi_code
  from (
 select if(work_type <> '邮政工单', duty_network_code, accept_network_code)        as network_code
       ,if(work_type <> '邮政工单', duty_network_name, accept_network_name)        as network_name
       ,'sqs00000009'           as kpi_code
       ,'客诉率'                as kpi_name
       ,sum(if(work_type = '邮政工单' and accept_proxy_code <> '888888', 1, 0)) + sum(if(work_type <> '邮政工单' and duty_proxy_code <> '888888', 1, 0))     as kpi_fz
       ,0                       as kpi_fm
       ,2                       as kpi_type
       ,dt                      as dt
  from jms_dm.dm_waybill_customer_complaint_detail_new_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt <='{{ execution_date | cst_ds }}'
   and is_delete = 2
 group by if(work_type <> '邮政工单',duty_network_code, accept_network_code),if(work_type <> '邮政工单', duty_network_name, accept_network_name),dt
union all
 select network_code            as network_code
       ,name                    as network_name
       ,'sqs00000009'           as kpi_code
       ,'客诉率'                as kpi_name
       ,0                       as kpi_fz
       ,(sum(tak_cnt) * 0.15) + (sum(arr_ctn) * 0.85)                       as kpi_fm
       ,2                       as kpi_type
       ,dt                      as dt
  from jms_dm.dm_customer_complaint_network_arr_pick_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt <='{{ execution_date | cst_ds }}'
  and network_type = '6'
  and date_type = 1
 group by network_code,name,dt
 ) t
group by network_code,network_name,dt
 distribute by dt,kpi_code
 ;

---遗失率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'遗失率'                   as kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,sum(kpi_fz)                as kpi_values
       ,1000000                        as kpi_param
       ,'郭瑞琳'                   as kpi_user
       ,'当月'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_sqs_loss_network_detail_mid_dt+jms_dwd.dwd_netwrok_metric_dictionary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000005'              as kpi_code
from (
select network_code           as network_code
       ,network_name         as network_name
       ,'sqs00000005'           AS kpi_code
       ,'遗失率'                as kpi_name
       ,sum(a.kpi_fz)           as kpi_fz
       ,sum(a.kpi_fm)           as kpi_fm
       ,2                       as kpi_type
       ,dt                      as dt
  from (
SELECT network_code          as network_code
      ,network_name          as network_name
      ,'sqs00000005'         AS kpi_code
      ,'遗失率'              as kpi_name
      ,sum(cast(1/network_count as decimal(16,4))) as kpi_fz
      ,0 as kpi_fm
      ,dt
  from jms_dm.dm_sqs_loss_network_detail_mid_dt
 where dt >=trunc('{{ execution_date | cst_ds }}','mm') and dt <='{{ execution_date | cst_ds }}'
  and loss_type='遗失'
 group by network_code,network_name,dt
 union all
select network_code as network_code
      ,network_name as network_name
      ,'sqs00000005'         AS kpi_code
      ,'遗失率'              as kpi_name
      ,0  as kpi_fz
      ,sum(metric_value)     as kpi_fm
      ,dt
  from jms_dwd.dwd_netwrok_metric_dictionary_dt
 where dt >=trunc('{{ execution_date | cst_ds }}','mm') and dt <='{{ execution_date | cst_ds }}'
   and metric_name in ('收件量','派件量')
---    and network_type='6'
group by network_code,network_name,dt
  ) a
group by network_code,network_name,dt
) t group by network_code,network_name,dt
 distribute by dt,kpi_code
 ;

---遗失率2
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'遗失率2'                   as kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,sum(kpi_fz)                as kpi_values
       ,1000000                        as kpi_param
       ,'郭瑞琳'                   as kpi_user
       ,'当月'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_sqs_loss_network_detail_mid_dt+jms_dwd.dwd_netwrok_metric_dictionary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs000000047'              as kpi_code
from (
select network_code           as network_code
       ,network_name         as network_name
       ,'sqs000000047'           AS kpi_code
       ,'遗失率2'                as kpi_name
       ,sum(a.kpi_fz)           as kpi_fz
       ,sum(a.kpi_fm)           as kpi_fm
       ,2                       as kpi_type
       ,dt                      as dt
  from (
SELECT network_code          as network_code
      ,network_name          as network_name
      ,'sqs000000047'         AS kpi_code
      ,'遗失率2'              as kpi_name
      ,sum(cast(1/network_count as decimal(16,4))) as kpi_fz
      ,0 as kpi_fm
      ,dt
  from jms_dm.dm_sqs_loss_network_detail_mid_dt
 where dt >=trunc('{{ execution_date | cst_ds }}','mm') and dt <='{{ execution_date | cst_ds }}'
  and loss_type='遗失'
 group by network_code,network_name,dt
 union all
select network_code as network_code
      ,network_name as network_name
      ,'sqs000000047'         AS kpi_code
      ,'遗失率2'               as kpi_name
      ,0                     as kpi_fz
      ,sum(metric_value)     as kpi_fm
      ,dt
  from jms_dwd.dwd_netwrok_metric_dictionary_dt
 where dt >=trunc('{{ execution_date | cst_ds }}','mm') and dt <='{{ execution_date | cst_ds }}'
   and metric_name in ('派件量')
---    and network_type='6'
group by network_code,network_name,dt
  ) a
group by network_code,network_name,dt
) t group by network_code,network_name,dt
 distribute by dt,kpi_code
 ;

---8.2破损率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'破损率'                   as kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,sum(kpi_fz)                as kpi_values
       ,1000000                        as kpi_param
       ,'申家明'                   as kpi_user
       ,'回刷90天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_sqs_worn_waybill_detail_dt+jms_dwd.dwd_netwrok_metric_dictionary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000007'              as kpi_code
from (
select network_code           as network_code
       ,network_name          as network_name
       ,'sqs00000007'           AS kpi_code
       ,'破损率'                as kpi_name
       ,sum(a.kpi_fz)           as kpi_fz
       ,sum(a.kpi_fm)           as kpi_fm
       ,2                       as kpi_type
       ,dt                      as dt
  from (
SELECT resp_network_code     as network_code
      ,resp_network_name     as network_name
      ,'sqs00000007'         as kpi_code
      ,'破损率'              as kpi_name
      ,sum(waybill_no_num)   as kpi_fz
      ,0     as kpi_fm
      ,dt
  from jms_dm.dm_sqs_worn_waybill_detail_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',90) and dt <='{{ execution_date | cst_ds }}'
   and is_delete = 1
   and computer_flag = 1
   and warn_worn_flag = 1
group by resp_network_code,resp_network_name,dt
union all
select network_code as network_code
       ,network_name as network_name
       ,'sqs00000007'           AS kpi_code
       ,'破损率'                as kpi_name
       ,0   as  kpi_fz
       ,sum(metric_value) kpi_fm
       ,dt
   from jms_dwd.dwd_netwrok_metric_dictionary_dt
  where dt >=date_sub('{{ execution_date | cst_ds }}',90) and dt <='{{ execution_date | cst_ds }}'
    and metric_name in ('收件量','派件量')
  group by network_code,network_name,dt
) a
group by network_code,network_name,dt
) t group by network_code,network_name,dt
 distribute by dt,kpi_code
 ;


---10.七星潭加盟制排名


insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'七星潭加盟制排名'            as kpi_name
       ,1                          as kpi_type
       ,kpi_fz                     as kpi_fz
       ,1                          as kpi_fm
       ,kpi_fz                     as kpi_values
       ,1                          as kpi_param
       ,'季修魁'                   as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dwd.dwd_tab_cn_ei_prov_base_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000379'              as kpi_code
from (
select b.agent_code             as network_code
       ,b.agent_name            as network_name
       ,'bis00000379'           as kpi_code
       ,'七星潭加盟制排名'      as kpi_name
       ,a.rn                    as kpi_fz
       ,1                       as kpi_fm
       ,1                       as kpi_type
       ,a.dt                    as dt
from (
SELECT
    t1.provinceCode,
    t1.provincename,
    t1.score,
    t1.scoreranking,
    t2.rn,
    '{{ execution_date | cst_ds }}'  as dt
FROM(SELECT *
    FROM (SELECT
            t.PROVINCE_CODE AS provinceCode,
            t.PROVINCE_NAME AS provincename ,
            t.score ,
            t.HY_RANK AS scoreranking,
            ROW_NUMBER() OVER(PARTITION BY PROVINCE_NAME, company ORDER BY t.CREATE_TIME DESC) AS rn1
        FROM jms_dwd.dwd_tab_cn_ei_prov_base_dt t
        WHERE to_date(t.ACHIEVE_DATE) = '{{ execution_date | cst_ds }}'
          AND DT >=date_add('{{ execution_date | cst_ds }}',-15)
          AND DT <='{{ execution_date | cst_ds }}'
          AND t.company = 'HTKY' AND t.subitem = '快递指数总分'
          ) a WHERE rn1=1
    ) t1
LEFT JOIN
(SELECT * FROM
        ( SELECT company,provinceCode,provincename,totalScores,
                 ROW_NUMBER() OVER(PARTITION BY provinceCode, provincename ORDER BY CAST(totalScores AS decimal(15,10)) DESC) AS rn
        FROM
            (
            SELECT t.company,t.provinceCode,t.provincename,min(t.score) AS totalScores
            FROM (
                SELECT t.company ,
                       t.PROVINCE_CODE AS provinceCode,
                       t.PROVINCE_NAME AS provincename,
                       t.score,
                       ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE, PROVINCE_NAME, company ORDER BY t.CREATE_TIME DESC) AS rnw
                 FROM jms_dwd.dwd_tab_cn_ei_prov_base_dt t
                WHERE to_date(t.ACHIEVE_DATE) = '{{ execution_date | cst_ds }}'
                  AND DT >=date_add('{{ execution_date | cst_ds }}',-15)  AND DT <='{{ execution_date | cst_ds }}'
                  ---and dt = '{{ execution_date | cst_ds }}'
                  AND t.company IN ('HTKY', 'ZTO', 'YTO', 'STO', 'YUNDA')
                  AND t.subitem = '快递指数总分'
              ) t
            WHERE rnw = 1
            GROUP BY t.company,t.provinceCode,t.provincename
           ) t
        ) b WHERE company = 'HTKY'
) t2
  ON t1.provinceCode = t2.provinceCode
) a
 join (select agent_code,agent_name,virt_code,virt_name
             from jms_dim.dim_network_whole_massage
            group by agent_code,agent_name,virt_code,virt_name
) b
  on substr(a.provincename,1,2)=substr(b.agent_name,1,2)
)  t  distribute by dt,kpi_code
;

---11.桃花岛上门取件低于75分网点数量

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'桃花岛上门取件低于75分网点数量'                   as kpi_name
       ,1                          as kpi_type
       ,kpi_fz                     as kpi_fz
       ,1                          as kpi_fm
       ,kpi_fz                     as kpi_values
       ,1                          as kpi_param
       ,'季修魁'                   as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'代理区维度指标，取jms_dm.dm_tab_door_pick_up_dm'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'ops00000154'              as kpi_code
from (
select b.agent_code             AS network_code
       ,b.agent_name            as network_name
       ,'ops00000154'           as kpi_code
       ,'桃花岛上门取件低于75分网点数量'      as kpi_name
       ,count(1)                as kpi_fz
       ,1                       as kpi_fm
       ,1                       as kpi_type
       ,a.dt                    as dt
from
(
SELECT
    organization_code AS network_code,
    organization_name AS network_name,
    sum(CASE WHEN take_parts_rate >= 99 AND take_parts_rate <= 100 THEN 20
             WHEN take_parts_rate >= 98 AND take_parts_rate < 99 THEN 16
             WHEN take_parts_rate >= 97 AND take_parts_rate < 98 THEN 14
             WHEN take_parts_rate >= 95 AND take_parts_rate < 97 THEN 12
             WHEN take_parts_rate >= 90 AND take_parts_rate < 95 THEN 10
             WHEN take_parts_rate >= 85 AND take_parts_rate < 90 THEN 6
             WHEN take_parts_rate >= 0 AND take_parts_rate < 85 THEN 0
           END) +
    sum(CASE WHEN cancellation_rate >= 0 AND cancellation_rate <= 7 THEN 30
             WHEN cancellation_rate > 7 AND cancellation_rate <= 8 THEN 26
             WHEN cancellation_rate > 8 AND cancellation_rate <= 9 THEN 22
             WHEN cancellation_rate > 9 AND cancellation_rate <= 10 THEN 18
             WHEN cancellation_rate > 10 AND cancellation_rate <= 12 THEN 14
             WHEN cancellation_rate > 12 AND cancellation_rate <= 15 THEN 10
             WHEN cancellation_rate > 15 AND cancellation_rate <= 20 THEN 6
             WHEN cancellation_rate > 20 AND cancellation_rate <= 30 THEN 2
             WHEN cancellation_rate > 30 AND cancellation_rate <= 100 THEN 0
           END) +
    sum(CASE WHEN default_rate = 0 THEN 15
             WHEN default_rate > 0 AND default_rate <= 0.01 THEN 10
             WHEN default_rate > 0.01 AND default_rate <= 0.3 THEN 6
             WHEN default_rate > 0.3 AND default_rate <= 1 THEN 3
             WHEN default_rate > 1 AND default_rate < 100 THEN 0
           END) +
    SUM(CASE WHEN complaint_rate >= 0 AND complaint_rate <= 0.03 THEN 35
             WHEN complaint_rate > 0.03 AND complaint_rate <= 0.04 THEN 28
             WHEN complaint_rate > 0.04 AND complaint_rate <= 0.06 THEN 25
             WHEN complaint_rate > 0.06 AND complaint_rate <= 0.08 THEN 20
             WHEN complaint_rate > 0.08 AND complaint_rate <= 0.10 THEN 14
             WHEN complaint_rate > 0.10 AND complaint_rate <= 0.20 THEN 12
             WHEN complaint_rate > 0.2 AND complaint_rate <= 0.5 THEN 10
             WHEN complaint_rate > 0.5 AND complaint_rate <= 1 THEN 4
             WHEN complaint_rate > 1 AND complaint_rate <= 100 THEN 0
           END) AS sm_score,
    DT
FROM
    (SELECT
        organization_code,
        organization_name,
        sum(total_single_quantity) AS total_single_quantity,
        CASE
            WHEN sum(pick_up_quantity) = 0 THEN 0
            ELSE sum(take_parts_and_measure_in_time) / sum(pick_up_quantity) * 100
        END AS take_parts_rate,
        CASE
            WHEN sum(total_single_quantity) = 0 THEN 0
            ELSE sum(cancellation_amount) / sum(total_single_quantity) * 100
        END AS cancellation_rate,
        CASE
            WHEN sum(amount_of_breach_of_contract) = 0 THEN 0
            ELSE sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100
        END AS default_rate,
        CASE
            WHEN sum(total_single_quantity) = 0 THEN 0
            ELSE sum(complaints) / sum(total_single_quantity) * 100
        END AS complaint_rate,
        '{{ execution_date | cst_ds }}'  AS  DT,
        'D'           AS  stat_period
    FROM
        jms_dm.dm_tab_door_pick_up_dm a
    WHERE dt ='{{ execution_date | cst_ds }}'
    GROUP BY
        organization_code,
        organization_name
        )  t
GROUP BY
    organization_code,
    organization_name,
    DT
    ) a
 join (select code,agent_code,agent_name,virt_code,virt_name
             from jms_dim.dim_network_whole_massage
---             WHERE agent_name in ('甘肃代理区','青海代理区','宁夏代理区')
            group by agent_code,agent_name,virt_code,virt_name,code
      ) b
  on a.network_code= b.code
 where a.sm_score <75
group by b.agent_code
       ,b.agent_name
       ,a.dt
  ) tt distribute by dt,kpi_code;

---12.进港出港转运及时率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from (
select dt              as stat_date
       ,'D'                        as stat_period
       ,end_center_code            as network_code
       ,end_center_name            as network_name
       ,'进港转运及时率'           as kpi_name
       ,2                          as kpi_type
       ,sum(timely_num)            as kpi_fz
       ,sum(case when untimely_type not in ('无二段码','末端机构不符') then opt_num else 0 end )     as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'王梦磊'                   as kpi_user
       ,'3天'                      as redo_date_cnt
       ,'中心维度指标，取jms_dws.dm_centersend_timerate_summary_v2_dt,t-2时效指标'   as remark
       ,'t-2'             as kpi_timely
       ,dt             as dt
       ,'bis00000279'              as kpi_code
  from jms_dm.dm_centersend_timerate_summary_v2_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',3) and dt <=date_sub('{{ execution_date | cst_ds }}',1)
 group by end_center_code,end_center_name,dt

union all

select dt              as stat_date
       ,'D'                        as stat_period
       ,start_code            as network_code
       ,start_name            as network_name
       ,'出港转运及时率'           as kpi_name
       ,2                          as kpi_type
       ,sum(transfer_intime_account)            as kpi_fz
       ,(sum(send_account) - sum(temporary_dispatch_account)-sum(route_lack_account)-sum(error_send_account))      as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'季修魁'                   as kpi_user
       ,'2天'                      as redo_date_cnt
       ,'中心维度指标，取jms_dm.dm_cn_departure_transfer_timely_route_agg_dt,t-2时效指标'   as remark
       ,'t-2'             as kpi_timely
       ,dt                as dt
       ,'bis00000153'              as kpi_code
  from jms_dm.dm_cn_departure_transfer_timely_route_agg_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',3) and dt <=date_sub('{{ execution_date | cst_ds }}',1)
 group by start_code,start_name,dt
) t
distribute by dt,kpi_code;

---13.送货上门率（七星潭）

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from (
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_site_code             as network_code
       ,max(scan_site_name)        as network_name
       ,'送货上门率'                 as kpi_name
       ,2                          as kpi_type
       ,sum(is_door_count)- sum(is_xusign_count)          as kpi_fz
       ,sum(is_timely_count)       as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'马诚'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000090'              as kpi_code
  from jms_dm.dm_door_sign_rate_sum_dt
 where  dt ='{{ execution_date | cst_ds }}' and type='1'   and ifnull(scan_site_code,'0') != 'Test02'
 group by scan_site_code,dt
union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_site_code            as network_code
       ,max(scan_site_name)            as network_name
       ,'送货上门率（七星潭）'               as kpi_name
       ,2                          as kpi_type
       ,sum(is_door_count) - sum(is_xusign_count)           as kpi_fz
       ,sum(is_timely_count)     as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'马诚'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000372'              as kpi_code
  from jms_dm.dm_door_sign_rate_sum_dt
 where  dt ='{{ execution_date | cst_ds }}' and ordersource_name = '七星潭' and type='1'   and ifnull(scan_site_code,'0') != 'Test02'
 group by scan_site_code,dt

union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_site_code            as network_code
       ,max(scan_site_name)            as network_name
       ,'收派员上门率'               as kpi_name
       ,2                          as kpi_type
       ,sum(pp_door_cn)           as kpi_fz
       ,sum(pp_sign_cn)     as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'马诚'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000408'              as kpi_code
  from jms_dm.dm_door_sign_rate_sum_dt
 where  dt ='{{ execution_date | cst_ds }}'  and type='1'   and ifnull(scan_site_code,'0') != 'Test02'
 group by scan_site_code,dt


union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_site_code            as network_code
       ,max(scan_site_name)            as network_name
       ,'驿站上门率'               as kpi_name
       ,2                          as kpi_type
       ,sum(is_stagesignanddoor_count)           as kpi_fz
       ,sum(pp_stage_cn)     as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'马诚'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000407'              as kpi_code
  from jms_dm.dm_door_sign_rate_sum_dt
 where  dt ='{{ execution_date | cst_ds }}'  and type='1'   and ifnull(scan_site_code,'0') != 'Test02'
 group by scan_site_code,dt
) t
distribute by dt,kpi_code;

---14.当日出库率  store_over_total 当日出库量，store_in_total 当日入库量，

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scansitecode             as network_code
       ,inputsite                as network_name
       ,'末端监控-当日出库率'       as kpi_name
       ,2                          as kpi_type
       ,sum(store_over_total)      as kpi_fz
       ,sum(store_in_total)        as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'侯文龙'                     as kpi_user
       ,'7天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_network_end_dispatch_diff_rate'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis0000000467'              as kpi_code
  from jms_dm.dm_network_end_dispatch_diff_rate
 where dt>=date_sub('{{ execution_date | cst_ds }}',7) and  dt<='{{ execution_date | cst_ds }}'
 group by scansitecode,inputsite,dt
distribute by dt,kpi_code;


---15 '收件量','发件量','到件量','派件量','签收量'

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code             as network_code
       ,network_name             as network_name
       ,CASE WHEN metric_name='收件量' then '收件量'
             WHEN metric_name='发件量' then '发件量'
             WHEN metric_name='到件量' then '到件量'
             WHEN metric_name='派件量' then '派件量'
             WHEN metric_name='签收量' then '签收量'
        end                        as  kpi_name
       ,1                          as kpi_type
       ,sum(metric_value)          as kpi_fz
       ,1                          as kpi_fm
       ,sum(metric_value)          as kpi_values
       ,1                          as kpi_param
       ,'黄志军'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dwd.dwd_netwrok_metric_dictionary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,CASE WHEN metric_name='收件量' then 'ops00000032'
             WHEN metric_name='发件量' then 'ops00000035'
             WHEN metric_name='到件量' then 'ops00000146'
             WHEN metric_name='派件量' then 'bis00000322'
             WHEN metric_name='签收量' then 'ops00000037'
        end                               as  kpi_code
  from jms_dwd.dwd_netwrok_metric_dictionary_dt
 where dt>=date_sub('{{ execution_date | cst_ds }}',31) and dt <='{{ execution_date | cst_ds }}'   ---回刷31天
 group by network_code,network_name,metric_name,dt
distribute by dt,kpi_code;

----15

-- 虚假揽收率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'虚假揽收率'               as  kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'熊伟'                     as kpi_user
       ,'2天'                      as redo_date_cnt
       ,'网点维度指标，取分子虚假揽收数jms_dm.dm_waybill_false_taking_dt,分母应交件jms_dm.dm_waybill_collect_intime_taking_agg'   as remark
       ,'t-1'                      as kpi_timely
       ,dt                         as dt
       ,'bis00000390'              as  kpi_code
from (
    select
        network_code
        ,network_name
        ,'bis00000390'             as kpi_code
        ,'虚假揽收率'              as kpi_name
        ,nvl(sum(kpi_fz), 0)       as kpi_fz
        ,nvl(sum(kpi_fm), 0)       as kpi_fm
        ,2                         as kpi_type
        ,dt                        as dt
    from (
        -- 虚假揽收数
        select
            receive_network_code       as network_code
            ,receive_network_name      as network_name
            ,nvl(count(*),0)           as kpi_fz
            ,0                         as kpi_fm
            ,dt
        from
            jms_dm.dm_waybill_false_taking_dt
        where  dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
        group by receive_network_code,receive_network_name, dt
        union all
        -- 应交件票数
        select
        receive_network_code       as network_code
        ,receive_network_name      as network_name
        ,0                                  as kpi_fz
        ,nvl(sum(should_hand_over_num),0)   as kpi_fm
        ,dt
        from
        jms_dm.dm_waybill_collect_intime_taking_agg
        where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
        group by receive_network_code,receive_network_name,dt
    ) tbl
        group by network_code,network_name,dt
) t group by network_code,network_name,dt
distribute by dt,kpi_code;


-- 虚假揽收票数
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,receive_network_code               as network_code
       ,receive_network_name               as network_name
       ,'虚假揽收票数'               as  kpi_name
       ,1                          as kpi_type
       ,nvl(count(*),0)                as kpi_fz
       ,1                          as kpi_fm
       ,nvl(count(*),0)                as kpi_values
       ,1                          as kpi_param
       ,'熊伟'                     as kpi_user
       ,'2天'                      as redo_date_cnt
       ,'网点维度指标，取分子虚假揽收数jms_dm.dm_waybill_false_taking_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000371'              as  kpi_code
  from jms_dm.dm_waybill_false_taking_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
 group by receive_network_code,receive_network_name, dt
distribute by dt,kpi_code;


---16.退转件及时率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from
(
select dt                          as stat_date
       ,'D'                        as stat_period
       ,actual_network_code               as network_code
       ,max(actual_network_name)               as network_name
       ,'退转件及时率'             as  kpi_name
       ,2                          as kpi_type
       ,max(total_is_timely_true)  as kpi_fz
       ,max(total_num)             as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'马诚'                     as kpi_user
       ,'30天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_reback_timely_rate_sum'   as remark
       ,'t-3'             as kpi_timely
       ,dt                         as dt
       ,'bis00000381'              as  kpi_code
  from jms_dm.dm_reback_timely_rate_sum
 where dt >=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}'
 group by actual_network_code,dt
 union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,actual_network_code               as network_code
       ,max(actual_network_name)               as network_name
       ,'拦截件罚款金额'           as  kpi_name
       ,2                          as kpi_type
       ,sum(waybill_money)         as kpi_fz
       ,1                          as kpi_fm
       ,sum(waybill_money)         as kpi_values
       ,1                          as kpi_param
       ,'马诚'                     as kpi_user
       ,'30天'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_reback_timely_rate_sum'   as remark
       ,'t-3'             as kpi_timely
       ,dt                         as dt
       ,'bis00000410'              as  kpi_code
  from jms_dm.dm_reback_timely_rate_sum
 where dt >=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}' and type_flg=2
 group by actual_network_code,dt
 union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,actual_network_code               as network_code
       ,max(actual_network_name)               as network_name
       ,'退转件罚款金额'           as  kpi_name
       ,2                          as kpi_type
       ,sum(waybill_money)         as kpi_fz
       ,1                          as kpi_fm
       ,sum(waybill_money)         as kpi_values
       ,1                          as kpi_param
       ,'马诚'                     as kpi_user
       ,'30天'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_reback_timely_rate_sum'   as remark
       ,'t-3'             as kpi_timely
       ,dt                         as dt
       ,'bis00000409'              as  kpi_code
  from jms_dm.dm_reback_timely_rate_sum
 where dt >=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}' and type_flg=1
 group by actual_network_code,dt
union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,actual_network_code               as network_code
       ,max(actual_network_name)               as network_name
       ,'拦截件'           as  kpi_name
       ,2                          as kpi_type
       ,sum(waybill_num)         as kpi_fz
       ,1                          as kpi_fm
       ,sum(waybill_num)         as kpi_values
       ,1                          as kpi_param
       ,'马诚'                     as kpi_user
       ,'30天'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_reback_timely_rate_sum'   as remark
       ,'t-3'             as kpi_timely
       ,dt                         as dt
       ,'bis00000411'              as  kpi_code
  from jms_dm.dm_reback_timely_rate_sum
 where dt >=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}' and type_flg=2
 group by actual_network_code,dt
union all
select dt                          as stat_date
       ,'D'                        as stat_period
       ,actual_network_code               as network_code
       ,max(actual_network_name)               as network_name
       ,'普通退转'           as  kpi_name
       ,2                          as kpi_type
       ,sum(waybill_num)         as kpi_fz
       ,1                          as kpi_fm
       ,sum(waybill_num)         as kpi_values
       ,1                          as kpi_param
       ,'马诚'                     as kpi_user
       ,'30天'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_reback_timely_rate_sum'   as remark
       ,'t-3'             as kpi_timely
       ,dt                         as dt
       ,'bis00000412'              as  kpi_code
  from jms_dm.dm_reback_timely_rate_sum
 where dt >=date_sub('{{ execution_date | cst_ds }}',30) and dt<='{{ execution_date | cst_ds }}' and type_flg=1
 group by actual_network_code,dt
) t
distribute by dt,kpi_code;

---王圣雅
--16 七星潭 规划揽收 时效签收 达成率 达成票数 总票数
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select date(plan_cainiao_platform_date)   as stat_date
       ,'D'                               as stat_period
       ,start_pick_network_code           as network_code
       ,start_pick_network_name           as network_name
       ,'七星潭达成率(出港)'                 as kpi_name
       ,2                                 as kpi_type
       ,sum(cainiao_pres_platform_reach_sum)         as kpi_fz
       ,sum(cainiao_pres_platform_total)             as kpi_fm
       ,null                              as kpi_values
       ,100                               as kpi_param
       ,'王圣雅'                          as kpi_user
       ,'11天'                            as redo_date_cnt
       ,'代理区维度指标，取jms_dm.dm_agency_province_pre_reach_rate_plan_dt'   as remark
       ,'t-1'             as kpi_timely
       ,date(plan_cainiao_platform_date)             as dt
       ,'bis00000399'                                as kpi_code
----  from jms_dm.dm_agency_province_pre_reach_rate_plan_dt
  from jms_dm.dm_net_pre_reach_rate_plan_dt        ----modify jixk 2023-11-16
where date(plan_cainiao_platform_date) between date_add('{{ execution_date | cst_ds }}', -10)  and  '{{ execution_date | cst_ds }}'
  and dt BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND date_add('{{ execution_date | cst_ds }}', 5)
  and cainiao_platform_name is not null
  and cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
  and (is_pres_sign = '1'
    or is_pres_sign = '0')
  and date_time is not null
 group by start_pick_network_code,start_pick_network_name,date(plan_cainiao_platform_date)
distribute by dt,kpi_code;

---到车准点率   tms00000014
---发车准点率   tms00000012
---运行准点率   tms00000013


insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from
(select dt                         as stat_date
       ,'D'                        as stat_period
       ,send_network_code                 as network_code
       ,send_network_name                 as network_name
       ,'到车准点率'                 as kpi_name
       ,2                          as kpi_type
       ,sum(case when is_ontime_flag='1' then 1 else 0 end)            as kpi_fz
       ,sum(case when is_shipment_flag='1' then 1 else 0 end)          as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'季修魁'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'任务明细jms_dm.dm_yl_shipment_trunk_detail_dt'   as remark
       ,'t-2'                      as kpi_timely
       ,dt                         as dt
       ,'tms00000014'              as kpi_code
 from jms_dm.dm_yl_shipment_trunk_detail_dt
where dt >= date_add('{{ execution_date | cst_ds }}',-7) and dt<=date_add('{{ execution_date | cst_ds }}',-1)
  and stat_type = '3'
group by send_network_code,send_network_name,dt
union all
select dt                         as stat_date
       ,'D'                        as stat_period
       ,send_network_code                 as network_code
       ,send_network_name                 as network_name
       ,'运行准点率'                 as kpi_name
       ,2                          as kpi_type
       ,sum(case when is_ontime_flag='1' then 1 else 0 end)            as kpi_fz
       ,sum(case when is_shipment_flag='1' then 1 else 0 end)          as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'季修魁'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'任务明细jms_dm.dm_yl_shipment_trunk_detail_dt'   as remark
       ,'t-2'                      as kpi_timely
       ,dt                         as dt
       ,'tms00000013'              as kpi_code
 from jms_dm.dm_yl_shipment_trunk_detail_dt
where dt >= date_add('{{ execution_date | cst_ds }}',-7) and dt<=date_add('{{ execution_date | cst_ds }}',-1)
  and stat_type = '2'
group by send_network_code,send_network_name,dt
union all
select dt                         as stat_date
       ,'D'                        as stat_period
       ,send_network_code                 as network_code
       ,send_network_name                 as network_name
       ,'发车准点率'                 as kpi_name
       ,2                          as kpi_type
       ,sum(case when is_ontime_flag='1' then 1 else 0 end)            as kpi_fz
       ,sum(case when is_shipment_flag='1' then 1 else 0 end)          as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'季修魁'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'任务明细jms_dm.dm_yl_shipment_trunk_detail_dt'   as remark
       ,'t-2'                      as kpi_timely
       ,dt                         as dt
       ,'tms00000012'              as kpi_code
 from jms_dm.dm_yl_shipment_trunk_detail_dt
where dt >= date_add('{{ execution_date | cst_ds }}',-7) and dt<=date_add('{{ execution_date | cst_ds }}',-1)
  and stat_type = '1'
group by send_network_code,send_network_name,dt
) t
distribute by dt,kpi_code
;

----网点到车准点率
with shipment_end_stop_d_tmp as (
select *
  from (
select shipment_no,actual_arrival_time,planned_arrival_time,app_arrival_time,sort_num
       ,row_number() over(partition by shipment_no order by sort_num desc)  as rn1
  from jms_dwd.dwd_tmsnew_shipment_stop_dt where dt='{{ execution_date | cst_ds }}'
  ) a where rn1 = 1),

shipment_day_d_tmp_site as (
 select
    tt.sendnetworkcode  as financial_center_code,
    tt.sendnetworkname  as financial_center_name,
    count(1) shipment_cnt,
    sum(case when (unix_timestamp(plannedarrivaltime)-unix_timestamp(actualarrivaltime)) >= 0 then 1 else 0 end ) as ontime_cnt,
    nvl(sum(case when tt.actualarrivaltime is not null then 1 else 0 end), 0) - nvl(sum( case when (unix_timestamp(plannedarrivaltime)-unix_timestamp(actualarrivaltime)) >= 0 then 1 else 0 end),0) late_cnt,
    4 ontime_type,
    to_date(tt.actualdeparturetime)  as dt
from
    (
    select
        t.start_code as sendnetworkcode,
        t.start_name as sendnetworkname,
        count(1) over () total ,
        t.planned_departure_time as planneddeparturetime,
        t.actual_departure_time as actualdeparturetime,
        stop2.planned_arrival_time as plannedarrivaltime,
        stop2.actual_arrival_time as actualarrivaltime
    from (select actual_departure_time,planned_departure_time,shipment_no,shipment_state,shipment_name,a.id,cnt,start_code,start_name,end_code,end_name,b.network_type as start_type,c.network_type as end_type
            from (
           select actual_departure_time,planned_departure_time,shipment_no,shipment_state,shipment_name,id,cnt,start_code,start_name,end_code,end_name
             from jms_dwd.dwd_tmsnew_shipment_union_base_dt
            where dt>=date_add('{{ execution_date | cst_ds }}',-15)
             and shipment_state <> 5 and table_type=2
            ) a
          left join jms_dim.dim_network_whole_massage b
            on a.start_code = b.code
          left join jms_dim.dim_network_whole_massage c
            on a.end_code = c.code
          where b.network_type in (6,5)     ---网点
            and c.network_type =4     ---中心
         ) t
    left join shipment_end_stop_d_tmp stop2
      on t.shipment_no = stop2.shipment_no
    )  tt
where to_date(tt.actualdeparturetime) ='{{ execution_date | cst_ds }}'
group by
    tt.sendnetworkcode,
    tt.sendnetworkname,
    to_date(tt.actualdeparturetime)
)

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select '{{ execution_date | cst_ds }}'                          as stat_date
       ,'D'                        as stat_period
       ,financial_center_code             as network_code
       ,financial_center_name             as network_name
       ,'网点到车准点率'           as kpi_name
       ,2                          as kpi_type
       ,sum(ontime_cnt)            as kpi_fz
       ,sum(shipment_cnt)          as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'季修魁'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取明细任务表jms_dwd.dwd_tmsnew_shipment_union_base_dt+jms_dwd.dwd_tmsnew_shipment_stop_dt'   as remark
       ,'t-1'             as kpi_timely
       ,'{{ execution_date | cst_ds }}' as dt
       ,'tms00000016'              as kpi_code
 from shipment_day_d_tmp_site
where dt ='{{ execution_date | cst_ds }}'
  and ontime_type = 4
group by financial_center_code,financial_center_name
distribute by dt,kpi_code
;

---郭瑞玲
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                      as stat_date
       ,'D'                    as stat_period --统计频度
       ,collect_scansitecode   as network_code
       ,collect_inputsite      as network_name
       ,'散货及时率'             as kpi_name
       ,2                      as kpi_type --1量值2率值
       ,sum(send_in_time)      as kpi_fz   --指标分子
       ,sum(send_total)        as kpi_fm   --指标分母
       ,cast(sum(send_in_time)/sum(send_total) as decimal(16,4))   as kpi_values  --指标值
       ,100                    as kpi_param  --指标参数(百分之,百万分之，万分之,1/100/10000)
       ,'郭瑞玲'                as kpi_user
       ,'1天'                  as redo_date_cnt
       ,'网点维度指标，取散货及时率表jms_dm.dm_collect_analysis_source_agg'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000146'     AS kpi_code
     from jms_dm.dm_collect_analysis_source_agg
     where dt='{{ execution_date | cst_ds }}' and schedule_num in ('一散','二散','三散')  --jms上是一散+二散的合计，三散属于散货时效异常的不算
     group by collect_scansitecode,collect_inputsite,dt
 distribute by dt,kpi_code
;
---95电联率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                 as stat_date
       ,'D'               as stat_period
       ,network_code      as network_code
       ,network_name      as network_name
       ,'95电联率'         as kpi_name
       ,2                 as kpi_type
       ,sum(t1_tel_cnt)   as kpi_fz
       ,sum(t1_mark_cnt)  as kpi_fm
       ,null              as kpi_values
       ,100               as kpi_param
       ,'王梦磊'          as kpi_user
       ,'1天'             as redo_date_cnt
       ,'网点维度指标，取订单表jms_dwd.dwd_yl_oms_oms_order_incre_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'ccm0000000058'     AS kpi_code
  from jms_dm.dm_operate_area_mark_telephone_summary_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by kpi_code,network_code,network_name,dt
 distribute by dt,kpi_code
;
---桃花岛指标
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
  from (
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-取件及时率'             as kpi_name
       ,2                                       as kpi_type
       ,take_parts_and_measure_in_time          as kpi_fz
       ,pick_up_quantity                        as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000062'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-取消率'                     as kpi_name
       ,2                                       as kpi_type
       ,cancellation_amount                     as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000061'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-投诉率'                 as kpi_name
       ,2                                       as kpi_type
       ,complaints                              as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000064'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-爽约率'                 as kpi_name
       ,2                                       as kpi_type
       ,amount_of_breach_of_contract            as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000063'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
) tt
distribute by dt,kpi_code;

---------中转次数
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,taking_agent_code                       as network_code
       ,taking_agent_name                       as network_name
       ,'实际中转次数'                            as kpi_name
       ,2                                       as kpi_type
       ,sum(front_actual_zz)                    as kpi_fz
       ,sum(front_number)                       as kpi_fm
       ,null                                    as kpi_values
       ,1                                       as kpi_param
       ,'马诚'                                   as kpi_user
       ,'1天'                                    as redo_date_cnt
       ,'代理区维度指标，取始发代理区到全国的数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'bis00000232'                           as kpi_code
  from jms_dm.dm_all_transfer_count_bak_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by taking_agent_code,taking_agent_name,dt
 distribute by dt,kpi_code;

---时效签收平均时长 bis00000263
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,start_agent_code                        as network_code
       ,start_agent_name                        as network_name
       ,'时效签收平均时长'                         as kpi_name
       ,2                                       as kpi_type
       ,sum(aging_sign_use_time)                as kpi_fz
       ,sum(aging_sign_num)                     as kpi_fm
       ,null                                    as kpi_values
       ,1                                       as kpi_param
       ,'王梦磊'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'代理区维度指标，取始发代理区到全国的数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'bis00000263'                           as kpi_code
  from jms_dws.dws_sign_aging_summary_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by start_agent_code,start_agent_name,dt
 distribute by dt,kpi_code;

---add jixk 20230906
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
from (
select t1.dt                             as stat_date
       ,'D'                              as stat_period
       ,senderdtributecode               as network_code
       ,t2.name                          as network_name
       ,'装载票数'                       as kpi_name
       ,2                                as kpi_type
       ,sum(linecount)                   as kpi_fz
       ,null                             as kpi_fm
       ,sum(linecount)                   as kpi_values
       ,100                              as kpi_param
       ,'禹铄'                           as kpi_user
       ,'1天'                            as redo_date_cnt
       ,'jms_dm.dm_tms_trunk_load_factor_basic_dt'   as remark
       ,'t-1'                            as kpi_timely
       ,t1.dt                            as dt
       ,'pps00000016'                    as kpi_code
from jms_dm.dm_tms_trunk_load_factor_basic_dt            t1
left join jms_dim.dim_network_whole_massage              t2
on t1.senderdtributecode = t2.code
where t1.dt>=date_sub('{{ execution_date | cst_ds }}',2) and t1.dt<='{{ execution_date | cst_ds }}'
and LENGTH (t1.shipmentname)- LENGTH(REPLACE(t1.shipmentname,'-','')) = 1 and (t1.grade <> 4 or t1.grade is null or t1.grade = '')
group by senderdtributecode,t2.name,t1.dt

union ALL
 select t1.dt                            as stat_date
       ,'D'                              as stat_period
       ,senderdtributecode               as network_code
       ,t2.name                          as network_name
       ,'核载票数'                       as kpi_name
       ,2                                as kpi_type
       ,sum(vehiclecount)                as kpi_fz
       ,null                             as kpi_fm
       ,sum(vehiclecount)                as kpi_values
       ,100                              as kpi_param
       ,'禹铄'                           as kpi_user
       ,'1天'                            as redo_date_cnt
       ,'jms_dm.dm_tms_trunk_load_factor_basic_dt'   as remark
       ,'t-1'                            as kpi_timely
       ,t1.dt                            as dt
       ,'bis0000000506'                  as kpi_code
  from jms_dm.dm_tms_trunk_load_factor_basic_dt            t1
  left join jms_dim.dim_network_whole_massage              t2
    on t1.senderdtributecode = t2.code
 where t1.dt>=date_sub('{{ execution_date | cst_ds }}',2) and t1.dt<='{{ execution_date | cst_ds }}'
  and LENGTH (t1.shipmentname)- LENGTH(REPLACE(t1.shipmentname,'-','')) = 1 and (t1.grade <> 4 or t1.grade is null or t1.grade = '')    ----直发逻辑 add jixk 2023-12-27
 group by senderdtributecode,t2.name,t1.dt

union ALL
 select t1.dt                            as stat_date
       ,'D'                              as stat_period
       ,senderdtributecode               as network_code
       ,t2.name                          as network_name
       ,'装载重量'                       as kpi_name
       ,2                                as kpi_type
       ,sum(lineweight)                  as kpi_fz
       ,null                             as kpi_fm
       ,sum(lineweight)                  as kpi_values
       ,100                              as kpi_param
       ,'禹铄'                           as kpi_user
       ,'1天'                            as redo_date_cnt
       ,'jms_dm.dm_tms_trunk_load_factor_basic_dt'   as remark
       ,'t-1'                            as kpi_timely
       ,t1.dt                            as dt
       ,'pps00000008'                    as kpi_code
  from jms_dm.dm_tms_trunk_load_factor_basic_dt            t1
  left join jms_dim.dim_network_whole_massage              t2
    on t1.senderdtributecode = t2.code
 where t1.dt>=date_sub('{{ execution_date | cst_ds }}',2) and t1.dt<='{{ execution_date | cst_ds }}'
  and LENGTH (t1.shipmentname)- LENGTH(REPLACE(t1.shipmentname,'-','')) = 1 and (t1.grade <> 4 or t1.grade is null or t1.grade = '')    ----直发逻辑 add jixk 2023-12-27
 group by senderdtributecode,t2.name,t1.dt


union ALL
 select t1.dt                            as stat_date
       ,'D'                              as stat_period
       ,senderdtributecode               as network_code
       ,t2.name                          as network_name
       ,'核载重量'                      as kpi_name
       ,2                                as kpi_type
       ,sum(vehicleweight)               as kpi_fz
       ,null                             as kpi_fm
       ,sum(vehicleweight)               as kpi_values
       ,100                              as kpi_param
       ,'禹铄'                           as kpi_user
       ,'1天'                            as redo_date_cnt
       ,'jms_dm.dm_tms_trunk_load_factor_basic_dt'   as remark
       ,'t-1'                            as kpi_timely
       ,t1.dt                               as dt
       ,'bis0000000507'                  as kpi_code
  from jms_dm.dm_tms_trunk_load_factor_basic_dt            t1
  left join jms_dim.dim_network_whole_massage              t2
    on t1.senderdtributecode = t2.code
 where t1.dt>=date_sub('{{ execution_date | cst_ds }}',2) and t1.dt<='{{ execution_date | cst_ds }}'
  and LENGTH (t1.shipmentname)- LENGTH(REPLACE(t1.shipmentname,'-','')) = 1 and (t1.grade <> 4 or t1.grade is null or t1.grade = '')    ----直发逻辑 add jixk 2023-12-27
 group by senderdtributecode,t2.name,t1.dt
) a
distribute by dt,kpi_code
;

-----中心漏扫  jms_dm.dm_tab_cn_center_scan_lack_agg_dt

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from (
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_code                  as network_code
       ,scan_name                  as network_name
       ,'到件漏扫'                 as kpi_name
       ,1                          as kpi_type
       ,sum(arrival_lack_cnt)      as kpi_fz
       ,null                       as kpi_fm
       ,sum(arrival_lack_cnt)      as kpi_values
       ,100                        as kpi_param
       ,'申家明'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'ops0000000158'              as kpi_code
  from jms_dm.dm_tab_cn_center_scan_lack_agg_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by scan_code,scan_name,dt
 union all
 select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_code                  as network_code
       ,scan_name                  as network_name
       ,'发件漏扫'                 as kpi_name
       ,1                          as kpi_type
       ,sum(send_lack_cnt)      as kpi_fz
       ,null                       as kpi_fm
       ,sum(send_lack_cnt)      as kpi_values
       ,100                        as kpi_param
       ,'申家明'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'ops0000000159'              as kpi_code
  from jms_dm.dm_tab_cn_center_scan_lack_agg_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by scan_code,scan_name,dt
 union all
 select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_code                  as network_code
       ,scan_name                  as network_name
       ,'应扫描量'                 as kpi_name
       ,1                          as kpi_type
       ,sum(ought_scan_cnt)      as kpi_fz
       ,null                       as kpi_fm
       ,sum(ought_scan_cnt)      as kpi_values
       ,100                        as kpi_param
       ,'申家明'                     as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_door_sign_rate_sum_dt,t-1时效指标'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'ops0000000160'              as kpi_code
  from jms_dm.dm_tab_cn_center_scan_lack_agg_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by scan_code,scan_name,dt
 ) a
distribute by dt,kpi_code;

----------车件分离

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select t1.dt                               as stat_date
       ,'D'                                as stat_period
       ,load_station_code                  as network_code
       ,load_station_name                  as network_name
       ,'车件分离'                         as kpi_name
       ,1                                  as kpi_type
       ,sum(bill_separated_cnt)            as kpi_fz
       ,null                               as kpi_fm
       ,sum(bill_separated_cnt)            as kpi_values
       ,100                                as kpi_param
       ,'季修魁'                           as kpi_user
       ,'1天'                              as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_waybill_transfer_separated_load_agg,t-2时效指标'   as remark
       ,'t-2'                              as kpi_timely
       ,t1.dt                              as dt
       ,'bis00000154'                      as kpi_code
  from jms_dm.dm_waybill_transfer_separated_load_agg            t1
 where dt =date_sub('{{ execution_date | cst_ds }}',1)
 group by load_station_code,load_station_name,t1.dt
distribute by dt,kpi_code;


-------------服务质量  （监控工单）


---客诉量1 sqs00000010

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,if(work_type <> '邮政工单', duty_network_code, accept_network_code)          as network_code
       ,if(work_type <> '邮政工单', duty_network_name, accept_network_name)          as network_name
       ,'客诉量'                  as kpi_name
       ,1                          as kpi_type
       ,sum(if(work_type = '邮政工单' and accept_proxy_code <> '888888', 1, 0)) + sum(if(work_type <> '邮政工单' and duty_proxy_code <> '888888', 1, 0))     as kpi_fz
       ,null                       as kpi_fm
       ,sum(if(work_type = '邮政工单' and accept_proxy_code <> '888888', 1, 0)) + sum(if(work_type <> '邮政工单' and duty_proxy_code <> '888888', 1, 0))     as kpi_values
       ,1                          as kpi_param
       ,'申家明'                   as kpi_user
       ,'当月'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.jms_dm.dm_waybill_customer_complaint_detail_new_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000010'              as kpi_code
  from jms_dm.dm_waybill_customer_complaint_detail_new_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt <='{{ execution_date | cst_ds }}'
   and is_delete = 2
 group by if(work_type <> '邮政工单', duty_network_code, accept_network_code),if(work_type <> '邮政工单', duty_network_name, accept_network_name),dt
distribute by dt,kpi_code;

---sqs00000008:破损票数

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,resp_network_code          as network_code
       ,resp_network_name          as network_name
       ,'破损票数'                  as kpi_name
       ,1                          as kpi_type
       ,sum(waybill_no_num)                       as kpi_fz
       ,null                       as kpi_fm
       ,sum(waybill_no_num)                   as kpi_values
       ,1                          as kpi_param
       ,'申家明'                   as kpi_user
       ,'90'                       as redo_date_cnt
       ,'网点维度指标，取jms_dm.jms_dm.dm_waybill_customer_complaint_detail_new_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000008'              as kpi_code
  from jms_dm.dm_sqs_worn_waybill_detail_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',90) and dt <='{{ execution_date | cst_ds }}'
   and is_delete = 1
   and computer_flag = 1
   and warn_worn_flag = 1
 group by resp_network_code,resp_network_name,dt
distribute by dt,kpi_code;

---sqs00000036:仲裁遗失票数

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'遗失票数'                 as kpi_name
       ,1                          as kpi_type
       ,sum(cast(1/network_count as decimal(16,4)))                       as kpi_fz
       ,null                       as kpi_fm
       ,sum(cast(1/network_count as decimal(16,4)))        as kpi_values
       ,1                          as kpi_param
       ,'郭瑞琳'                   as kpi_user
       ,'当月'                     as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_sqs_loss_network_detail_mid_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'sqs00000036'              as kpi_code
  from jms_dm.dm_sqs_loss_network_detail_mid_dt
 where dt >=trunc('{{ execution_date | cst_ds }}','mm') and dt <='{{ execution_date | cst_ds }}'
  and loss_type='遗失'
 group by network_code,network_name,dt
distribute by dt,kpi_code;

---ops00000003:中心操作量

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,scan_site_code             as network_code
       ,scan_site_name             as network_name
       ,'中心操作量'               as kpi_name
       ,1                          as kpi_type
       ,sum(oper_cnt)              as kpi_fz
       ,null                       as kpi_fm
       ,sum(oper_cnt)              as kpi_values
       ,1                          as kpi_param
       ,'王梦磊'                   as kpi_user
       ,'近3天'                    as redo_date_cnt
       ,'中心维度指标，取jms_dm.dm_center_transfer_summary_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'ops00000003'              as kpi_code
  from jms_dm.dm_center_transfer_summary_new_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',2)  and dt <='{{ execution_date | cst_ds }}'
   and sum_flag = '自然日'
 group by scan_site_code,scan_site_name,dt
 distribute by dt,kpi_code;


-------------服务质量  （监控工单）

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                              as stat_date
       ,'D'                            as stat_period
       ,resp_network_code              as network_code
       ,resp_network_name              as network_name
       ,'装车不规范次数'               as kpi_name
       ,1                              as kpi_type
       ,count(work_order_code)         as kpi_fz
       ,null                           as kpi_fm
       ,count(work_order_code)         as kpi_values
       ,1                              as kpi_param
       ,'季修魁'                       as kpi_user
       ,'近30天'                       as redo_date_cnt
       ,'jms_dwd.dwd_monitor_wo_dt'    as remark
       ,'t-1'                          as kpi_timely
       ,dt
       ,'sqs0000000054'                as kpi_code
  from (select work_order_code,resp_network_code,resp_network_name,to_date(create_time) as dt,max(cast(coalesce(real_penalty_amount,penalty_amount) as int )) as penalty_amount
          from jms_dwd.dwd_monitor_wo_dt
         where dt >=date_add('{{ execution_date | cst_ds }}',-45) and dt <='{{ execution_date | cst_ds }}'
           and to_date(create_time)>=date_add('{{ execution_date | cst_ds }}',-31) and to_date(create_time)<='{{ execution_date | cst_ds }}'
           and problem_type_code='05'
        group by work_order_code,resp_network_code,resp_network_name,to_date(create_time)
        ) a
 group by resp_network_code,resp_network_name,dt
 union all
select dt                              as stat_date
       ,'D'                            as stat_period
       ,resp_network_code              as network_code
       ,resp_network_name              as network_name
       ,'装车不规范罚款'               as kpi_name
       ,1                              as kpi_type
       ,sum(penalty_amount)         as kpi_fz
       ,null                           as kpi_fm
       ,sum(penalty_amount)         as kpi_values
       ,1                              as kpi_param
       ,'季修魁'                       as kpi_user
       ,'近30天'                       as redo_date_cnt
       ,'jms_dwd.dwd_monitor_wo_dt'    as remark
       ,'t-1'                          as kpi_timely
       ,dt
       ,'sqs0000000055'                as kpi_code
  from (select work_order_code,resp_network_code,resp_network_name,to_date(create_time) as dt,max(cast(real_penalty_amount as int )) as penalty_amount
          from jms_dwd.dwd_monitor_wo_dt
         where dt >=date_add('{{ execution_date | cst_ds }}',-45) and dt <='{{ execution_date | cst_ds }}'
           and to_date(create_time)>=date_add('{{ execution_date | cst_ds }}',-31) and to_date(create_time)<='{{ execution_date | cst_ds }}'
           and problem_type_code='05'
        group by work_order_code,resp_network_code,resp_network_name,to_date(create_time)
        ) a
 group by resp_network_code,resp_network_name,dt

 union all
select dt                              as stat_date
       ,'D'                            as stat_period
       ,resp_network_code              as network_code
       ,resp_network_name              as network_name
       ,'野蛮操作'                     as kpi_name
       ,1                              as kpi_type
       ,count(work_order_code)         as kpi_fz
       ,null                           as kpi_fm
       ,count(work_order_code)         as kpi_values
       ,1                              as kpi_param
       ,'季修魁'                       as kpi_user
       ,'近30天'                       as redo_date_cnt
       ,'jms_dwd.dwd_monitor_wo_dt'    as remark
       ,'t-1'                          as kpi_timely
       ,dt
       ,'sqs0000000053'                as kpi_code
  from (select work_order_code,resp_network_code,resp_network_name,to_date(create_time) as dt,max(cast(real_penalty_amount as int )) as penalty_amount
          from jms_dwd.dwd_monitor_wo_dt
         where dt >=date_add('{{ execution_date | cst_ds }}',-45) and dt <='{{ execution_date | cst_ds }}'
           and to_date(create_time)>=date_add('{{ execution_date | cst_ds }}',-31) and to_date(create_time)<='{{ execution_date | cst_ds }}'
           and problem_type_code='01'
        group by work_order_code,resp_network_code,resp_network_name,to_date(create_time)
        ) a
  group by resp_network_code,resp_network_name,dt
 distribute by dt,kpi_code;

----预警网点数 add 20230914
set spark.sql.crossJoin.enabled=true;
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select  dt                               as stat_date
       ,'D'                              as stat_period
       ,code                             as network_code
       ,name                             as network_name
       ,'预警网点'                       as kpi_name
       ,1                                as kpi_type
       ,null                             as kpi_fz
       ,null                             as kpi_fm
       ,target_value                     as kpi_values
       ,1                                as kpi_param
       ,'申家明'                         as kpi_user
       ,'1天'                            as redo_date_cnt
       ,'jms_dm.dm_mng_explosion_warehouse_network_punish_dt'     as remark
       ,'t-1'                            as kpi_timely
       ,dt                               as dt
       ,'bis0000000546'                  as kpi_code
  from (
    select t1.code
         , t1.name
         , if(t2.final_sign_network_code is not null, 1, 0) as target_value
         , t1.dt
    from (
             select code
                  , name
                  , date_mid_desc as dt
             from (
                      select code
                           , name
                      from jms_dim.dim_network_whole_massage
                     where network_type = 6
                        and name not like 'BEST%'
                        and agent_code is not null
                        and agent_code not in ('3021888', '540000', 'B800033') --总部市场部,西藏代理区,best西藏代理区

                        ---and is_enable=2
                  ) a
                      join
                  (
                      select date_mid_desc
                      from jms_dim.dim_date_info_base
                      where date_mid_desc <= '{{ execution_date | cst_ds }}'
                        and date_mid_desc >= date_add('{{ execution_date | cst_ds }}', -1)
                  ) b
                  on 1 = 1
         ) t1
             left join
         (
             select final_sign_network_code
                  , dt
             from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -1)
               and button_delete = 0
         ) t2
         on t1.code = t2.final_sign_network_code
             and t1.dt = t2.dt
) t
distribute by dt
,kpi_code
;


-----虚假签收率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
SELECT dt                                 as stat_date
        ,'D'                              as stat_period
        ,network_code                     as network_code
        ,network_name                     as network_name
        ,'虚假签收率'                       as kpi_name
        ,2                                as kpi_type
        ,sum(false_sign_cnt)              as kpi_fz
        ,sum(effectiveness_cnt)           as kpi_fm
        ,null                             as kpi_values
        ,10000                            as kpi_param
        ,'王圣雅'                          as kpi_user
        ,'2天'                            as redo_date_cnt
        ,'jms_dm.dm_sqs_false_sign_sum_dt'     as remark
        ,'t-1'                            as kpi_timely
        ,dt
        ,'sqs00000013'                  as kpi_code
from jms_dm.dm_sqs_false_sign_sum_dt
where dt >= date_add('{{ execution_date | cst_ds }}', -1)
  and dt <= '{{ execution_date | cst_ds }}'
  and data_type = 1
group by network_code, network_name,dt
distribute by dt
,kpi_code
;

-----转运中心漏扫率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
SELECT dt                                     as stat_date
        ,'D'                                  as stat_period
        ,scan_code                            as network_code
        ,scan_name                            as network_name
        ,'转运中心漏扫率'                        as kpi_name
        ,2                                    as kpi_type
        ,sum(arrival_lack_cnt+send_lack_cnt)  as kpi_fz
        ,sum(ought_scan_cnt * 2)              as kpi_fm
        ,null                             as kpi_values
        ,100                                 as kpi_param
        ,'申家明'                          as kpi_user
        ,'1天'                            as redo_date_cnt
        ,'jms_dm.dm_tab_cn_center_scan_lack_agg_dt'     as remark
        ,'t-1'                            as kpi_timely
        ,dt
        ,'ops0000000165'                  as kpi_code
from jms_dm.dm_tab_cn_center_scan_lack_agg_dt
where dt = '{{ execution_date | cst_ds }}'
group by scan_code,scan_name,dt
distribute by dt
,kpi_code
;

-----网点综合漏扫率
----（揽收漏扫票数+入仓漏扫票数+发件漏扫票数+到件漏扫票数+派件漏扫票数）/[网点应扫运单数（收入发）*3+网点应扫运单数（到派）*2]*100%，保留两位小数，两位之后的直接舍去
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select coalesce(a.dt,b.dt)                   as stat_date
       ,'D'                                  as stat_period
       ,coalesce(a.network_code,b.network_code) as network_code
       ,coalesce(a.network_name,b.network_name) as network_name
        ,'网点综合漏扫率'                        as kpi_name
        ,2                                    as kpi_type
        ,sum(nvl(a.taking_miss_total,0)+nvl(a.warehousing_miss_total,0)+nvl(a.send_miss_total,0)+nvl(b.is_arrival_missed_total,0)+nvl(b.is_deliver_missed_total,0))  as kpi_fz
        ,sum(nvl(a.total_num,0)*3+nvl(b.sign_bill_total,0)*2)   as kpi_fm
        ,null                             as kpi_values
        ,100                                 as kpi_param
        ,'郭瑞玲'                          as kpi_user
        ,'1天'                            as redo_date_cnt
        ,concat(a.tab1,',',b.tab2)        as remark
        ,'t-1'                              as kpi_timely
        ,coalesce(a.dt,b.dt)                as dt
        ,'ops0000000179'                    as kpi_code
  from
(
select  network_code
       ,network_name
       ,sum(taking_miss_total)        as taking_miss_total          ---揽收漏扫票数
       ,sum(warehousing_miss_total)   as warehousing_miss_total     ---入仓漏扫票数
       ,sum(send_miss_total)          as send_miss_total            ---发件漏扫票数
       ,sum(total_num)                as total_num                  ---网点应扫运单数（收入发）
       ,dt                            as dt
       ,'jms_dm.dm_taking_scan_miss_rate'   as tab1
  from jms_dm.dm_taking_scan_miss_rate           ---揽收漏扫
 where dt='{{ execution_date | cst_ds }}'
 group by network_name,network_code,dt
 ) a
 full join
 (
 select  sign_network_code                            as network_code
        ,sign_network_name                            as network_name
        ,sum(sign_bill_total)                         as sign_bill_total          ---签收应扫描运单总数
        ,sum(is_arrival_missed_total)                 as is_arrival_missed_total  ---到件漏扫总数
        ,sum(is_deliver_missed_total)                 as is_deliver_missed_total  ---出仓漏扫总数
        ,'jms_dm.dm_network_deliver_miss_scan_rate'   as tab2
        ,dt
   from jms_dm.dm_network_deliver_miss_scan_rate
  where dt='{{ execution_date | cst_ds }}'
  group by sign_network_code,sign_network_name,dt
 ) b
 on a.network_code = b.network_code
and a.dt = b.dt
group by coalesce(a.dt,b.dt),coalesce(a.network_code,b.network_code),coalesce(a.network_name,b.network_name),concat(a.tab1,',',b.tab2)
distribute by dt
,kpi_code
;

-----网点综合漏扫率（不包含入仓漏扫）  bis0000000566
----（揽收漏扫票数+发件漏扫票数+到件漏扫票数+派件漏扫票数）/[网点应扫运单数（收入发）*2+网点应扫运单数（到派）*2]*100%，保留两位小数，两位之后的直接舍去

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select coalesce(a.dt,b.dt)                   as stat_date
       ,'D'                                  as stat_period
       ,coalesce(a.network_code,b.network_code) as network_code
       ,coalesce(a.network_name,b.network_name) as network_name
        ,'网点综合漏扫率（不包含入仓漏扫）'           as kpi_name
        ,2                                      as kpi_type
        ,sum(nvl(a.taking_miss_total,0)+nvl(a.send_miss_total,0)+nvl(b.is_arrival_missed_total,0)+nvl(b.is_deliver_missed_total,0))  as kpi_fz
        ,sum(nvl(a.total_num,0)*2+nvl(b.sign_bill_total,0)*2)   as kpi_fm
        ,null                             as kpi_values
        ,100                                 as kpi_param
        ,'郭瑞玲'                          as kpi_user
        ,'1天'                            as redo_date_cnt
        ,concat(a.tab1,',',b.tab2)        as remark
        ,'t-1'                              as kpi_timely
        ,coalesce(a.dt,b.dt)                as dt
        ,'bis0000000566'                    as kpi_code
  from
(
select  network_code
       ,network_name
       ,sum(taking_miss_total)        as taking_miss_total          ---揽收漏扫票数
       ,sum(warehousing_miss_total)   as warehousing_miss_total     ---入仓漏扫票数
       ,sum(send_miss_total)          as send_miss_total            ---发件漏扫票数
       ,sum(total_num)                as total_num                  ---网点应扫运单数（收入发）
       ,dt                            as dt
       ,'jms_dm.dm_taking_scan_miss_rate'   as tab1
  from jms_dm.dm_taking_scan_miss_rate           ---揽收漏扫
 where dt='{{ execution_date | cst_ds }}'
 group by network_name,network_code,dt
 ) a
 full join
 (
 select  sign_network_code                            as network_code
        ,sign_network_name                            as network_name
        ,sum(sign_bill_total)                         as sign_bill_total          ---签收应扫描运单总数
        ,sum(is_arrival_missed_total)                 as is_arrival_missed_total  ---到件漏扫总数
        ,sum(is_deliver_missed_total)                 as is_deliver_missed_total  ---出仓漏扫总数
        ,'jms_dm.dm_network_deliver_miss_scan_rate'   as tab2
        ,dt
   from jms_dm.dm_network_deliver_miss_scan_rate
  where dt='{{ execution_date | cst_ds }}'
  group by sign_network_code,sign_network_name,dt
 ) b
 on a.network_code = b.network_code
and a.dt = b.dt
group by coalesce(a.dt,b.dt),coalesce(a.network_code,b.network_code),coalesce(a.network_name,b.network_name),concat(a.tab1,',',b.tab2)
distribute by dt
,kpi_code
;


-----24点前签收率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
SELECT
dt                                     as stat_date
    ,'D'                                  as stat_period
    ,final_sign_network_code              as network_code
    ,final_sign_network_name              as network_name
    ,'24点前签收率'                       as kpi_name
    ,2                                    as kpi_type
    ,sum(all_sign_24)                     as kpi_fz
    ,sum(need_sign_count)                 as kpi_fm
    ,null                                 as kpi_values
    ,100                                  as kpi_param
    ,'侯文龙'                             as kpi_user
    ,'1天'                                as redo_date_cnt
    ,'jms_dm.dm_terminal_sign_punctuality_rate_dt' as remark
    ,'t-1'                                as kpi_timely
    ,dt                                   as dt
    ,'bis0000000570'                      as kpi_code
from jms_dm.dm_terminal_sign_punctuality_rate_dt
where dt ='{{ execution_date | cst_ds }}'
group by final_sign_network_code
        ,final_sign_network_name
        ,dt
distribute by dt,kpi_code
;

---全履约来源的打标电联率  add jixk 2023-11-07
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from
(
 select dt                            as stat_date
        ,'D'                          as stat_period
        ,network_code                 as network_code
        ,network_name                 as network_name
        ,'全履约来源的打标电联率'         as kpi_name
        ,2                            as kpi_type
        ,sum(tel_cnt)              as kpi_fz
        ,sum(mark_cnt)             as kpi_fm
        ,null                         as kpi_values
        ,100                          as kpi_param
        ,'王梦磊'                      as kpi_user
        ,'1天'                        as redo_date_cnt
        ,'jms_dm.dm_operate_area_mark_telephone_summary_day_dt'  as remark
        ,'t-1'             as kpi_timely
        ,dt
        ,'bis0000000569'     AS kpi_code
  from jms_dm.dm_operate_area_mark_telephone_summary_day_dt
 where dt ='{{ execution_date | cst_ds }}'
 group by network_code,network_name,dt
 ) t1
 distribute by dt,kpi_code
 ;

 --网点-整体上传及时率

 insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
 select * from
 (
  select dt                            as stat_date
         ,'D'                          as stat_period
         ,network_code                 as network_code
         ,network_name                 as network_name
         ,'网点-整体上传及时率'           as kpi_name
         ,2                            as kpi_type
         ,sum(all_cnt)                               as kpi_fz
         ,(sum(all_cnt)+sum(all_no_cnt))             as kpi_fm
         ,null                         as kpi_values
         ,100                          as kpi_param
         ,'郭瑞玲'                      as kpi_user
         ,'1天'                        as redo_date_cnt
         ,'jms_dm.dm_upload_in_time_count_dt'  as remark
         ,'t-1'             as kpi_timely
         ,dt
         ,'bis00000187'     AS kpi_code
   from jms_dm.dm_upload_in_time_count_dt
  where dt between date_add('{{ execution_date | cst_ds }}',-0)  and '{{ execution_date | cst_ds }}'
    and cnt_type=1
  group by network_code,network_name,dt
  ) t1
  distribute by dt,kpi_code
  ;

-----市场部业务量
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'收件量'                    as kpi_name
       ,1                          as kpi_type
       ,null                       as kpi_fz
       ,null                       as kpi_fm
       ,sum(taking_cnt)            as kpi_values
       ,1                          as kpi_param
       ,'季修魁'                    as kpi_user
       ,'7天'                      as redo_date_cnt
       ,'市场部业务量'               as remark
       ,'t-1'                      as kpi_timely
       ,dt                         as dt
       ,'bis0000000565'            as kpi_code
  from jms_dm.dm_cust_target_analysis_sum_day_dt
 where dt between date_add('{{ execution_date | cst_ds }}',-7)  and '{{ execution_date | cst_ds }}'   ---回刷7天
 group by network_code,network_name,dt
distribute by dt,kpi_code;


-----出港未及时清仓率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
from (
select dt                                                                                                          as stat_date
       ,'D'                                                                                                        as stat_period
       ,start_code                                                                                                 as network_code
       ,start_name                                                                                                 as network_name
       ,'出港未及时清仓率'                                                                                         as kpi_name
       ,2                                                                                                          as kpi_type
       ,sum(case when is_timely = 3 then is_store end)                                                             as kpi_fz
       ,sum(1)                                                                                                     as kpi_fm
       ,null                                                                                                       as kpi_values
       ,100                                                                                                        as kpi_param
       ,'季修魁'                                                                                                   as kpi_user
       ,'2天'                                                                                                      as redo_date_cnt
       ,'中心维度指标，取jms_dm.dm_cn_departure_transfer_timely_route_agg_dt,t-2时效指标'                          as remark
       ,'t-2'                                                                                                      as kpi_timely
       ,dt                                                                                                         as dt
       ,'ops0000000204'                                                                                            as kpi_code
  from jms_dm.dm_cn_departure_transfer_timely_route_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',3) and dt <=date_sub('{{ execution_date | cst_ds }}',1)
 group by start_code,start_name,dt
) t
distribute by dt,kpi_code;

insert overwrite table jms_dwm.dwm_overall_target_network_sum_day_dt partition (dt)
select a.stat_date
       ,b.network_type
       ,a.network_code
       ,a.network_name
       ,b.fran_code
       ,b.fran_name
       ,b.agent_code
       ,b.agent_name
       ,b.virt_code
       ,b.virt_name
       ,a.kpi_code
       ,a.kpi_name
       ,a.kpi_type
       ,a.kpi_fz
       ,a.kpi_fm
       ,a.kpi_fz
       ,a.kpi_param
       ,c.principal_name
       ,d.principal
       ,b.principal
       ,a.dt
from (
select * from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-1'
union all
select date_add(stat_date,1) as stat_date
       ,stat_period
       ,network_code
       ,network_name
       ,kpi_name
       ,kpi_type
       ,kpi_fz
       ,kpi_fm
       ,kpi_values
       ,kpi_param
       ,kpi_user
       ,redo_date_cnt
       ,remark
       ,kpi_timely
       ,date_add(dt,1)  as dt
       ,kpi_code
  from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-2'
union all
select date_add(stat_date,2) as stat_date
       ,stat_period
       ,network_code
       ,network_name
       ,kpi_name
       ,kpi_type
       ,kpi_fz
       ,kpi_fm
       ,kpi_values
       ,kpi_param
       ,kpi_user
       ,redo_date_cnt
       ,remark
       ,kpi_timely
       ,date_add(dt,2)  as dt
       ,kpi_code
  from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-3'
) a
left join jms_dim.dim_network_whole_massage b
  on a.network_code = b.code
left join (select agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]  as principal_name
             from jms_dim.dim_network_whole_massage where network_type =2   ---代理区负责人
            group by agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]
          ) c
  on b.agent_code = c.agent_code
left join (select code,name,principal from jms_dim.dim_network_whole_massage where network_type = '3'
            group by code,name,principal
          ) d
  on b.fran_code =d.code
distribute by dt;
